package com.baosight.xinreport.sql.read;

import cn.hutool.core.lang.Console;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;

import java.util.ArrayList;
import java.util.List;

public class SqlReadTest2 {
    public static void main(String[] args) {
        String sql = "select\n" +
                "            T5624.\"CALC_PERIOD\" as \"F37315\",\n" +
                "            T5624.\"EMP_CODE\" as \"F35581\",\n" +
                "            T37292.\"POST_NAME\" as \"F37277\",\n" +
                "            T5625.\"ORG_FULL_NAME\" as \"F154538\",\n" +
                "            T5620.\"ORG_NAME\" || T5620.\"POST_NAME\" as \"F64868\",\n" +
                "            T37534.\"IS_FULL_TIME\" as \"F37526\",\n" +
                "            T48817.\"PROJECT_NAME\" as \"F209230\",\n" +
                "            T49174.\"VOCATION_QUALIFY_NAME\" as \"F209509\",\n" +
                "            T49180.\"TITLE_NAME\" as \"F66340\",\n" +
                "            T48430.\"MAJOR_NAME\" as \"F66346\",\n" +
                "            T48430.\"LEVEL_NAME\" as \"F66345\",\n" +
                "            T49155.\"SPEC_TASK_TYPE\" as \"F182264\",\n" +
                "            T49167.\"LANGUAGE_CATEGORY\" as \"F93311\",\n" +
                "            T37293.\"MEMBER_NAME\" as \"F37288\",\n" +
                "            T42557.\"ASSESS_RESULT\" as \"F140479\",\n" +
                "            T48804.\"REWARD_LIBRARY\" as \"F135824\",\n" +
                "            T48339.\"CONTRACT_TYPE_NAME\" as \"F55867\",\n" +
                "            T49097.\"CERT_TYPE_NAME\" as \"F209544\"\n" +
                "        from\n" +
                "            \"BI\".\"T_BI_SALARY_EMP_POST\" T37292\n" +
                "            inner join \"BI\".\"T_BI_SALARY_EMP\" T5624 on T5624.\"SALARY_CONFIG_ID\" = T37292.\"SALARY_CONFIG_ID\"\n" +
                "            and T5624.\"EMP_CODE\" = T37292.\"EMP_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_WORK_EXP\" T5620 on T5624.\"CALC_PERIOD\" = T5620.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T5620.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T5620.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_SALARY_ORG\" T5625 on T5624.\"SALARY_CONFIG_ID\" = T5625.\"SALARY_CONFIG_ID\"\n" +
                "            and T5624.\"ORG_CODE\" = T5625.\"ORG_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_SKILL_LEVEL\" T48430 on T5624.\"CALC_PERIOD\" = T48430.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T48430.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T48430.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_SPECIALITY_QUAL\" T49180 on T5624.\"CALC_PERIOD\" = T49180.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T49180.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T49180.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_EMP_FAMILY\" T37293 on T5624.\"EMP_CODE\" = T37293.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T37293.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_REWARD_INFO\" T48804 on T5624.\"CALC_PERIOD\" = T48804.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T48804.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T48804.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_EDUCATION_EXP\" T37534 on T5624.\"CALC_PERIOD\" = T37534.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T37534.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T37534.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_ASSESS_PERFORMANCE\" T42557 on T5624.\"CALC_PERIOD\" = T42557.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T42557.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T42557.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_TRAIN_RECORD\" T48817 on T5624.\"CALC_PERIOD\" = T48817.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T48817.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T48817.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_SPEC_TASK\" T49155 on T5624.\"CALC_PERIOD\" = T49155.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T49155.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T49155.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_LANG\" T49167 on T5624.\"CALC_PERIOD\" = T49167.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T49167.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T49167.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_EMP_CONTRACT\" T48339 on T5624.\"EMP_CODE\" = T48339.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T48339.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_EMP_CERT_OTHER\" T49097 on T5624.\"CALC_PERIOD\" = T49097.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T49097.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T49097.\"TENANT_CODE\"\n" +
                "            inner join \"BI\".\"T_BI_VOCATION_QUALIFY\" T49174 on T5624.\"CALC_PERIOD\" = T49174.\"MONTH\"\n" +
                "            and T5624.\"EMP_CODE\" = T49174.\"EMP_CODE\"\n" +
                "            and T5624.\"TENANT_CODE\" = T49174.\"TENANT_CODE\"\n" +
                "        where\n" +
                "            (\n" +
                "                (\n" +
                "                    T5625.\"COMPANY_CODE1\" IN ('ROOT')\n" +
                "                    OR T5625.\"COMPANY_CODE2\" IN ('ROOT')\n" +
                "                    OR T5625.\"COMPANY_CODE3\" IN ('ROOT')\n" +
                "                    OR T5625.\"COMPANY_CODE4\" IN ('ROOT')\n" +
                "                    OR T5625.\"COMPANY_CODE5\" IN ('ROOT')\n" +
                "                    OR T5625.\"COMPANY_CODE6\" IN ('ROOT')\n" +
                "                    OR T5625.\"COMPANY_CODE7\" IN ('ROOT')\n" +
                "                    OR T5625.\"FIRST_ORG_CODE\" IN ('ROOT')\n" +
                "                    OR T5625.\"SECOND_ORG_CODE\" IN ('ROOT')\n" +
                "                    OR T5625.\"THIRD_ORG_CODE\" IN ('ROOT')\n" +
                "                    OR 'admin' = 'admin'\n" +
                "                )\n" +
                "                and T5625.\"TENANT_CODE\" =('BSTA')\n" +
                "            )\n" +
                "            and (\n" +
                "                T5624.\"CALC_PERIOD\" = '202406'\n" +
                "                AND T5624.\"EMP_TYPE_NAME\" <> '其他'\n" +
                "            )";
        try {
            List<String> tables = new ArrayList<>();
            List<String> columns = new ArrayList<>();
            parseSql(sql, tables, columns);
            System.out.println("Data Tables:");
            for (String table : tables) {
                System.out.println(table);
            }
            System.out.println("Columns:");
            for (String column : columns) {
                System.out.println(column);
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }

    public static void parseSql(String sql, List<String> tables, List<String> columns) throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(sql);
        if (statement instanceof Select) {
            Select select = (Select) statement;
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            // Get tables
            FromItem fromItem = plainSelect.getFromItem();


            if (fromItem instanceof Table) {
                tables.add(((Table) fromItem).getName());
            } else if (fromItem instanceof Join) {
//                Join join = (Join) fromItem;

            }
            List<Join> joins = plainSelect.getJoins();
            for (Join join : joins) {
                if (join instanceof Join ) {
                    String rightTableName = join.getRightItem().toString();
                    join.getRightItem().getClass();
                    if (join.getRightItem() instanceof Table) {
                        Table rightTable = (Table) join.getRightItem();
//                            rightTableName = rightTable.getAlias().getName();
                            rightTableName = rightTable.getName();
                    }
                    tables.add(rightTableName);
                }
            }
            // Get columns
            for (SelectItem item : plainSelect.getSelectItems()) {
                Console.log(item);
                if (item instanceof SelectExpressionItem) {
                    SelectExpressionItem expressionItem = (SelectExpressionItem) item;
                    columns.add(expressionItem.getExpression().toString());
                }
            }
        }
        Console.log(statement);
    }


}
